<?php


namespace App\Services\Base;


trait SqlBatchService
{

    public function update_batch($tableName = "", $multipleData = array())
    {

        if ($tableName && !empty($multipleData)) {

            // column or fields to update
            $updateColumn = array_keys($multipleData[0]);
            $referenceColumn = $updateColumn[0]; //e.g id
            unset($updateColumn[0]);
            $whereIn = "";

            $sql = "update " . $tableName . " set ";
            foreach ($updateColumn as $uColumn) {
                $sql .= "`" . $uColumn . "`" . " = case ";

                foreach ($multipleData as $data) {
                    $sql .= "when " . "`" . $referenceColumn . "`" . " = " . "'" . $data[$referenceColumn] . "'" . " then '" . $data[$uColumn] . "' ";
                }
                $sql .= "else " . "`" . $uColumn . "`" . " end, ";
            }
            foreach ($multipleData as $data) {
                $whereIn .= "'" . $data[$referenceColumn] . "', ";
            }
            $sql = rtrim($sql, ", ") . " where " . "`" . $referenceColumn . "`" . " in (" . rtrim($whereIn, ', ') . ")";
            return $sql;

        } else {
            return '';
        }

    }


    public function insert_batch($table, $arr_key, $arr, $split = '`')
    {

        $arrValues = array();

        if (empty($table) || !is_array($arr_key) || !is_array($arr)) {
            return false;
        }

        $sql = "insert into %s( %s ) values %s ";

        foreach ($arr as $k => $v) {
            $arrValues[$k] = "'" . implode("','", array_values($v)) . "'";
        }

        $sql = sprintf($sql, $table, "{$split}" . implode("{$split} ,{$split}", $arr_key) . "{$split}", "(" . implode(") , (", array_values($arrValues)) . ")");
        return $sql;
    }

    public function delete_batch($table, $where)
    {

        $sql = "delete from  " . $table . " where ";

        $i = 0;
        $count = count($where);
        foreach ($where as $key => $val) {

            end($val);      //指针指向最后一个 取出来的是最后一个值
            $last_key = key($val);
            $i++;

            foreach ($val as $title => $title_value) {
                $sql .= $title . " = " . "'" . $title_value . "'";
                if ($last_key != $title)
                    $sql .= " and ";
            }

            if ($i != $count) {
                $sql .= " or ";
            }

        }

        return $sql;
    }

    public function delete_batch_by($table, $where)
    {

        $sql = "delete from  " . $table . ' where ';

        foreach ($where as $key => $val) {
            $sql .= $key . " in (" . rtrim($val, ', ') . ")";
        }

        return $sql;
    }
}